package com.qqtem.grupo14;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseAdapter {
	private static DatabaseAdapter singletonInstance = null;

	/*** Database ***/
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private Context mContext;

	/*** Places Database ***/
	private static final String TAG = "DatabaseAdapter";
	public static final String _ID = "_id";
	public static final String NAME = "name";
	public static final String TYPE = "type";
	public static final String DESCRIPTION = "descr";
	public static final String TAGS = "tags";
	public static final String PHOTO = "photo";
	public static final String LAT = "latitude";
	public static final String LONG = "longitude";
	public static final String CREATEDBY = "created_by";

	private static final String DATABASE_CREATE = "create table places (_id integer primary key autoincrement, name text not null, type text not null, descr text not null, tags text not null, photo text, latitude int not null, longitude int not null, created_by int not null);";
	private static final String DATABASE_NAME = "data";
	private static final String DATABASE_TABLE = "places";
	private static final int DATABASE_VERSION = 6;

	/*** User Information ***/
	private static final String LOGIN = "login";
	private static final String USER_NAME = "name";
	private static final String PASS = "password";
	private static final String ABOUT = "about";
	private static final String EMAIL = "email";
	private static final String DATABASE_USER_CREATE = "create table users ( _id integer primary key autoincrement,"
			+ "login text not null, password text not null, name text not null, about text, email text not null);";
	private static final String DATABASE_USER_TABLE = "users";

	/*** User reviews ***/
	private static final String DATABASE_COMMENT_TABLE = "place_comment";
	private static final String PLACE_NAME = "name";
	private static final String PLACE_USERNAME = "username";
	private static final String PLACE_DESCRIPTION = "description";
	private static final String DATABASE_COMMENT_CREATE = "create table " +
	DATABASE_COMMENT_TABLE + "( _id integer primary key autoincrement, username not null ," +
			" description not null);";
	
	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

			db.execSQL(DATABASE_CREATE);
			db.execSQL(DATABASE_USER_CREATE);
			db.execSQL(DATABASE_COMMENT_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS places");
			db.execSQL("DROP TABLE IF EXISTS users");
			db.execSQL("DROP TABLE IF EXISTS place_comment");
			onCreate(db);
		}
	}

	private DatabaseAdapter(Context ctx) {
		this.mContext = ctx;
	}

	public static DatabaseAdapter createSingletonInstance(Context ctx) {
		if (singletonInstance == null) {
			singletonInstance = new DatabaseAdapter(ctx);
			return singletonInstance;
		} else {
			return singletonInstance;
		}
	}

	public static DatabaseAdapter getSingletonInstance() {
		return singletonInstance;
	}

	public void open() throws SQLException {
		mDbHelper = new DatabaseHelper(this.mContext);
		mDb = mDbHelper.getWritableDatabase();
		singletonInstance = this;
	}

	public void close() {
		mDbHelper.close();
	}
	
	/*** Stuff to store and retrieve user comments ***/
	public long createComment(String nome_estab, String usuario, String descr) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(PLACE_NAME, nome_estab);
		initialValues.put(PLACE_USERNAME, usuario);
		initialValues.put(PLACE_DESCRIPTION, descr);

		singletonInstance.open();
		long err = mDb.insert(DATABASE_COMMENT_TABLE, null, initialValues);
		singletonInstance.close();

		return err;
	}

	
	public Cursor fetchComment(String estab_nome) throws SQLException {
		Cursor c = null;

		singletonInstance.open();
		try {
			c = mDb.query(DATABASE_TABLE, new String[] { _ID,
					PLACE_NAME, PLACE_USERNAME, PLACE_DESCRIPTION },
					PLACE_NAME + "=" + "\"" + estab_nome
					+ "\"", null, null, null, null, null);

			if (!c.moveToFirst()) {
				singletonInstance.close();
				return null;
			}

		} catch (SQLException e) {
			singletonInstance.close();
			return null;
		}

		singletonInstance.close();
		return c;
	}

	/*** Stuff to store and retrieve an user ***/
	public long createUser(String login, String pass, String name,
		String email, String about) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(LOGIN, login);
		initialValues.put(PASS, pass);
		initialValues.put(USER_NAME, name);
		initialValues.put(EMAIL, email);
		initialValues.put(ABOUT, about);

		singletonInstance.open();
		long err = mDb.insert(DATABASE_USER_TABLE, null, initialValues);
		singletonInstance.close();

		return err;
	}

	public boolean updateUser(String login, String pass, String name,
			String email, String about) {
		ContentValues values = new ContentValues();
		values.put(LOGIN, login);
		values.put(PASS, pass);
		values.put(USER_NAME, name);
		values.put(EMAIL, email);
		values.put(ABOUT, about);

		return mDb.update(DATABASE_USER_TABLE, values, LOGIN + "=" + "\""
				+ login + "\"", null) > 0;
	}

	public boolean loginAvailable(String login) {
		return (fetchUser(login) == null);
	}

	public Cursor fetchUser(String login) throws SQLException {
		Cursor c = null;

		singletonInstance.open();
		try {
			c = mDb.query(DATABASE_USER_TABLE, new String[] { LOGIN, PASS,
					USER_NAME, EMAIL, ABOUT }, LOGIN + "=" + "\"" + login
					+ "\"", null, null, null, null, null);

			if (!c.moveToFirst()) {
				singletonInstance.close();
				return null;
			}

		} catch (SQLException e) {
			singletonInstance.close();
			return null;
		}

		singletonInstance.close();
		return c;
	}

	public boolean validateLogin(String login, String pass) {
		Cursor c = fetchUser(login);

		if (c != null) {
			if (c.getString(1).equals(pass))
				return true;
		}

		return false;
	}

	/*** Stuff to store and retrieve a location ***/
	public long createPlace(String name, String descr, String tags,
			String type, String photo, int latitude, int longitude,
			int createdBY) {
		
		ContentValues initialValues = new ContentValues();
		initialValues.put(NAME, name);
		initialValues.put(TYPE, type);
		initialValues.put(DESCRIPTION, descr);
		initialValues.put(TAGS, tags);
		initialValues.put(PHOTO, photo);
		initialValues.put(LAT, latitude);
		initialValues.put(LONG, longitude);
		initialValues.put(CREATEDBY, createdBY);
		
		singletonInstance.open();
		long err = mDb.insert(DATABASE_TABLE, null, initialValues);
		singletonInstance.close();
		
		return err;
	}

	public boolean deleteNote(long id) {
		return mDb.delete(DATABASE_TABLE, _ID + "=" + id, null) > 0;
	}

	public Cursor fetchAllPlaces() {
		return mDb.query(DATABASE_TABLE, new String[] { _ID, NAME, TYPE,
				DESCRIPTION, TAGS, PHOTO, LAT, LONG, CREATEDBY }, null, null,
				null, null, null);
	}

	public Cursor fecthPlace(String nome) throws SQLException {
		Cursor c = null;

		singletonInstance.open();
		try {
			c = mDb.query(DATABASE_TABLE, new String[] { _ID,
					NAME, TYPE, DESCRIPTION, TAGS, PHOTO, LAT, LONG, CREATEDBY },
					NAME + "=" + "\"" + nome
					+ "\"", null, null, null, null, null);

			if (!c.moveToFirst()) {
				singletonInstance.close();
				return null;
			}

		} catch (SQLException e) {
			singletonInstance.close();
			return null;
		}
		

		singletonInstance.close();
		return c;
	}
	
//	public Cursor fetchPlace(long id) throws SQLException {
//		Cursor mCursor = mDb.query(true, DATABASE_TABLE, new String[] { _ID,
//				NAME, TYPE, DESCRIPTION, TAGS, PHOTO, LAT, LONG, CREATEDBY },
//				_ID + "=" + id, null, null, null, null, null);
//		if (mCursor != null) {
//			mCursor.moveToFirst();
//		}
//
//		return mCursor;
//	}

	public boolean updatePlace(long id, String name, String descr, String tags,
			String type, String photo, int latitude, int longitude,
			int createdBy) {
		ContentValues args = new ContentValues();
		args.put(NAME, name);
		args.put(TYPE, type);
		args.put(DESCRIPTION, descr);
		args.put(TAGS, tags);
		args.put(PHOTO, photo);
		args.put(LAT, latitude);
		args.put(LONG, longitude);
		args.put(CREATEDBY, createdBy);

		return mDb.update(DATABASE_TABLE, args, _ID + "=" + id, null) > 0;
	}

}
